-- 회원 table ***********************************************************************************************************
select*from member_beer;
drop table member_beer;
create table member_beer(
	member_id varchar2(15) primary key,
	member_password varchar2(15) not null,
	email varchar2(50) not null,
	member_name varchar2(30),
	age number,
	gender char(1),
	address varchar2(50),
	drop_flag char(1)
);
create table test(
	test_id varchar2(2),
	test_name varchar2(2)
);


-- 맥주 table ************************************************************************************************************
select*from beer;
drop table beer;
create table beer(
	beer_id number primary key,
	beer_name varchar2(50) not null,
	beer_name_eng varchar2(50) not null,
	country varchar2(50) not null,
	country_eng varchar2(50) not null,
	brand varchar2(50) not null,
	brand_eng varchar2(50) not null,
	beer_type varchar2(10) not null,
	alcohol_degree varchar2(5) not null,
	beer_color varchar2(10) not null,
	beer_flavor varchar2(10),
	beer_price varchar2(10),
	beer_explanation varchar2(4000),
	etc varchar2(1000)
);

-- 블로그(게시판) table ***************************************************************************************************
select*from blog;
drop table blog;
create table blog(
	blog_no number primary key,
	blog_title varchar2(50) not null,
	blog_date date not null,
	content clob not null,
	member_id varchar2(15) not null,
	hits number not null,
	category_id varchar2(15),
	image_url varchar2(500),
	open_range varchar(15),
	constraint blog_member_id_fk foreign key(member_id) references member_beer(member_id)
);
--블로그 게시판 no (pk)
drop sequence blog_no;
create sequence blog_no
	nocycle
;
select blog_no.nextval from dual;

-- 게시물 댓글 table
select*from blog_reply;
drop table blog_reply;
create table blog_reply(
	reply_no number primary key,
	reply_date date not null,
	blog_no number not null,
	member_id varchar2(15) not null,
	reply_content varchar2(4000) not null,
	constraint reply_member_id_fk foreign key(member_id) references member_beer(member_id),
	constraint reply_blog_no_fk foreign key(blog_no) references blog(blog_no)
);

-- 맥주 평가 table ************************************************************************************************************
select*from estimate_beer;
drop table estimate_beer;
create table estimate_beer(
	estimate_no number primary key,
	beer_id number not null,
	member_id varchar2(15) not null,
	grade number(1) not null,
	constraint estimate_beer_id_fk foreign key(beer_id) references beer(beer_id),
	constraint estimate_member_id_fk foreign key(member_id) references member_beer(member_id)
);
--맥주평가 no (pk)
drop sequence estimate_no;
create sequence estimate_no
	nocycle
;
select estimate_no.nextval from dual;

-- 친구관계 table **********************************************************************************************************
select*from relationship;
drop table relationship;
create table relationship(
	relationship_no number primary key,
	member_id varchar2(15) not null,
	friend_id varchar2(15) not null,
	relationship_status varchar2(15) not null,
	constraint my_member_id_fk foreign key(member_id) references member_beer(member_id),
	constraint friend_id_fk foreign key(member_id) references member_beer(member_id)
);
--친구관계 no (pk)
drop sequence relationship_no;
create sequence relationship_no
	nocycle
;
select relationship_no.nextval from dual;

-- 관리자 table *******************************************************************************************************
select*from administrator;
drop table administrator;
create table administrator(
	admin_id varchar2(15) primary key,
	admin_name varchar2(30) not null,
	admin_gender char(1) not null,
	admin_email varchar2(30) not null,
	admin_address varchar2(50) not null,
	admin_department varchar2(10)
);

-- 공통 코드 table *********************************************************************************************************
-- 코드화하여 관리할 공통 코드를 정의하는 테이블(대분류)
-- ex) ML, 회원등급, 회원의 등급을 분류하는 카테고리를 코드화함
--      GD, 성별      , 성별을 분류하는 카테고리를 코드화함
create table common_code(
	common_code_key varchar2(10) primary key,
	common_code_value varchar2(10) not null,
	explanation varchar2(500)
);

-- 실제 사용할 공통 코드의 하위 코드(소분류) 
-- ex) admin,     관리자,     관리자 권한 부여,        ML(fk)
--      lev2,        우수회원,  우수회원 서비스 이용,  ML (fk)
--      lev1,        일반회원,  일반회원 서비스 이용,  ML(fk) 
--      M,           남자,        남자회원,                   GD(fk)
--      F,            여자,        여자회원,                   GD(fk)
create table sub_code(
	subcode_key varchar2(10) primary key,
	subcode_value varchar2(10) not null,
	subcode_explanation varchar2(500),
	common_code_key varchar2(10),
	constraint common_code_key_fk foreign key(common_code_key) references common_code(common_code_key)
);

drop sequence code_id;
create sequence code_id
	nocycle
;
select code_id.nextval from dual;

--***************************************** foreign key 설정************************************
-- on delete cascade : fk의 원본 레코드가 삭제되면 fk가 함께 삭제된다.
-- on delete set null : fk의 원본 레코드가 삭제되면 fk를 null 로 한다.
-- on update cascade : fk의 원본 레코드가 업데이트되면 fk도 함께 업데이트한다. 
--   => (주의!) on update cascade는 MySQL에서만 지원하므로, Oracle DB에선 Trigger 이용하도록
--**********************************************************************************************













